#!/usr/bin/env python3
# -*- coding: utf-8 -*-
' main entry '
__author__ = 'pingwu you'


'''
mysql-connector 是 MySQL 官方提供的驱动器。
'''

import mysql.connector as mysql
def init():    
    #如果没有设置autocommit，那么自动提交事务是关闭状态，会影响数据库表记录的提交；
    #但不影响create database这种操作
    conn = mysql.connect(
        host="192.168.0.35", user='root', password='770619')
    cs =conn.cursor()
    cs.execute("CREATE DATABASE python_test;")
    cs.execute("use python_test;")
    cs.execute('create table student (id varchar(20) primary key, name varchar(20))')
    cs.execute('insert into student (id, name) values (\'1\', \'张三\')')
    cs.execute('insert into student (id, name) values (\'2\', \'李四\')')
    cs.execute('insert into student (id, name) values (\'3\', \'王五\')')    
    #明确提交吧，否则可能insert update delete 语句无效，因为默认没有开启自动提交
    conn.commit()

def query():
    conn = mysql.connect(host="192.168.0.35", user='root', password='770619',database="python_test")
    cs =conn.cursor()
    cs.execute("select * from student where id=%s ",("1",))
    print(cs.fetchall())
    cs.execute("select * from student where id=%s ",["2",])
    print(cs.fetchall())
    cs.execute("select * from student where name like %s ",["%五%",])
    print(cs.fetchall())

## 注意，如果没有设置autocommit，那么自动提交事务是关闭状态
def trans():
    conn, cursor = None, None
    try:
        # 建立连接 注意，如果没有设置autocommit，那么自动提交事务是关闭状态
        conn = mysql.connect(
            host="192.168.0.35", user='root', password='770619',database="python_test",
            autocommit=False)  #如果没有设置autocommit，那么自动提交事务是关闭状态
        cursor = conn.cursor()                  
        cursor.execute("update student set name=%s where id=1",("新的id11111111",))
        cursor.execute("update student set name=%s where idaaaa=2",("新的id22222222",))
        cursor.execute("select * from student")
        print("查看结果：", cursor.fetchall())  
        ####必须手工提交，否则真的没有自动提交啊！！！      
        conn.commit()
    except Exception as e:
        conn.rollback()
        print("已触发回滚操作！")
    finally:        
        if cursor:
            cursor.close()
        if conn:
            conn.close()